SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF

DECLARE
	l_total_extracted	NUMBER;
	l_total_imported	NUMBER;		
	l_total_extracted_2 NUMBER;		
	l_total_imported_2 NUMBER;
	l_func_id			VARCHAR2(100):='CAPPOREC_APPROVED_SUPPLIER_LIST';
	l_code VARCHAR2(100):= UPPER('&1');

BEGIN
	select COUNT(1)
	into l_total_extracted
	from   po_approved_supplier_list pasl, 
	       hr_organization_information hoi
	where  pasl.using_organization_id = hoi.organization_id 
	and    hoi.org_information_context = 'Accounting Information'
	and    hoi.org_information1 =  (select set_of_books_id     		
     								from   gl_sets_of_books
     								where  upper(short_name) = upper(l_code));
	
	SELECT COUNT(1) 
	INTO l_total_imported
	FROM CRST_PO_APPROVED_SUPPLIER_LIST;

	SELECT COUNT(1)
	INTO l_total_extracted_2
	from po_asl_attributes
    	where using_organization_id = 
						(SELECT pasl.USING_ORGANIZATION_ID
						 FROM po_approved_supplier_list pasl, 
	       					hr_organization_information hoi
						 where  pasl.using_organization_id = hoi.organization_id and 
	       				 hoi.org_information_context = 'Accounting Information' and
 	       				 hoi.org_information1 =  (select set_of_books_id     		
     								from   gl_sets_of_books
     								where  upper(short_name) = upper(l_code))
						 )
	and asl_id = 			 
						 (SELECT pasl.ASL_ID
						  FROM po_approved_supplier_list pasl, 
	       					 hr_organization_information hoi
						  where  pasl.using_organization_id = hoi.organization_id and 
	       				  hoi.org_information_context = 'Accounting Information' and
 	       				  hoi.org_information1 =  (select set_of_books_id     		
     								from   gl_sets_of_books
     								where  upper(short_name) = upper(l_code))
						  );
	
	SELECT COUNT(1) 
	INTO l_total_imported_2
	FROM CRST_PO_ASL_ATTRIBUTES;

	dbms_output.put_line('Function: '||l_func_id);		
	dbms_output.put_line('Total Extracted: '||TO_CHAR(l_total_extracted)) ;
	dbms_output.put_line('Total Imported:  '||TO_CHAR(l_total_imported));
	dbms_output.put_line('Total Extracted_2: '||TO_CHAR(l_total_extracted)) ;
	dbms_output.put_line('Total Imported_2:  '||TO_CHAR(l_total_imported));

	IF l_total_extracted <> l_total_imported THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;	

	IF l_total_extracted_2 <> l_total_imported_2 THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;	
						
EXCEPTION
	WHEN OTHERS THEN	
		dbms_output.put_line('Function Error: '||l_func_id);			
END;
/
